package com.ruoyi;

import lombok.Data;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@SuppressWarnings("all")
public class MysqlDemo {


    // 1.加载JDBC驱动
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";

    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败：" + e);
        }
    }

    // 2.获取连接
    public static Connection getConnection(String url, String userName, String password) {
        Connection connection = null;
        try {
            // 创建连接
            connection = DriverManager.getConnection(url, userName, password);
        } catch (SQLException e) {
            System.out.println("获取连接失败：" + e);
        }
        return connection;
    }

    // 3. 获取数据库列表名称
    public static void getDataBase(String url, String userName, String password) {
        DatabaseMetaData databaseMetaData;
        ResultSet catalogResultSet = null;
        try (Connection connection = getConnection(url, userName, password)) {
            databaseMetaData = connection.getMetaData();
            catalogResultSet = databaseMetaData.getCatalogs();
            while (catalogResultSet.next()) {
                String databaseName = catalogResultSet.getString(1);
                // 去除系统表
                if ("information_schema".equals(databaseName)
                        || "mysql".equals(databaseName)
                        || "performance_schema".equals(databaseName)
                        || "sys".equals(databaseName)) {
                    continue;
                }
                System.out.println("DataBase : " + databaseName);
            }
        } catch (SQLException e) {
            System.out.println("获取数据库名称失败：" + e);
        } finally {
            close(catalogResultSet);
        }
    }

    // 4.获取数据库表名称
    public static void getTables(String url, String userName, String password) {
        // 1、连接数据库， 控制台打印出这个数据库里面所有的表
        ResultSet resultSet = null;
        try (Connection connection = getConnection(url, userName, password)) {
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            // 可用可不用
            String[] types = {"TABLE"};
            resultSet = databaseMetaData.getTables(connection.getCatalog(), null, "%", types);
            while (resultSet.next()) {
                System.out.println("database : " + resultSet.getString(1) + " ==== table : " + resultSet.getString(3));
            }
        } catch (SQLException e) {
            System.out.println("获取数据库所有表名失败：" + e);
        } finally {
            close(resultSet);
        }
    }

    // 5. 获取表的字段及类型
    public static void getColumns(String tableName, String url, String userName, String password) {
        String query = "SELECT * FROM " + tableName;
        try (Connection connection = getConnection(url, userName, password);
             PreparedStatement statement = connection.prepareStatement(query)) {
            ResultSetMetaData resultSetMetaData = statement.getMetaData();
            System.out.println("table : " + tableName);
            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                System.out.println("ColumnName : " + resultSetMetaData.getColumnName(i + 1));
                System.out.println("ColumnTypeName : " + resultSetMetaData.getColumnTypeName(i + 1));
                // 获取字段注释
                DatabaseMetaData dbmd = connection.getMetaData();
                ResultSet columnResultSet = dbmd.getColumns(null, null, tableName, resultSetMetaData.getColumnLabel(i + 1));
                if (columnResultSet.next()) {
                    String columnComment = columnResultSet.getString("REMARKS");
                    System.out.println("columnComment : " + columnComment);
                } else {
                    System.out.println("columnComment : " + "");
                }
            }
        } catch (SQLException e) {
            System.out.println("获取数据库表字段和类型失败：" + e);
        }
    }

    // 6.获取表字段对应的内容
    public static void getRows(String tableName, String url, String userName, String password) {
        String query = "SELECT * FROM " + tableName + " LIMIT 0, 10";
        ResultSet resultSet = null;
        try (Connection connection = getConnection(url, userName, password);
             PreparedStatement statement = connection.prepareStatement(query)) {
            ResultSetMetaData resultSetMetaData = statement.getMetaData();
            resultSet = statement.executeQuery();
            System.out.println("table : " + tableName);
            while (resultSet.next()) {
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    System.out.println(resultSetMetaData.getColumnName(i + 1) + ": " + resultSet.getString(i + 1));
                }
            }
        } catch (SQLException e) {
            System.out.println("获取数据库表字段对应内容失败：" + e);
        } finally {
            close(resultSet);
        }
    }

    // 7. 获取数据库相关信息
    public static void getDataBaseInfo(String url, String userName, String password) {
        ResultSet resultSet = null;
        try (Connection connection = getConnection(url, userName, password)) {
            DatabaseMetaData dbmd = connection.getMetaData();
            System.out.println("数据库已知的用户: " + dbmd.getUserName());
            System.out.println("数据库的系统函数: " + dbmd.getSystemFunctions());
            System.out.println("数据库的时间和日期函数: " + dbmd.getTimeDateFunctions());
            System.out.println("数据库的字符串函数: " + dbmd.getStringFunctions());
            System.out.println("数据库供应商用于 'schema' 的首选术语: " + dbmd.getSchemaTerm());
            System.out.println("数据库URL: " + dbmd.getURL());
            System.out.println("是否允许只读:" + dbmd.isReadOnly());
            System.out.println("数据库的产品名称:" + dbmd.getDatabaseProductName());
            System.out.println("数据库的版本:" + dbmd.getDatabaseProductVersion());
            System.out.println("驱动程序的名称:" + dbmd.getDriverName());
            System.out.println("驱动程序的版本:" + dbmd.getDriverVersion());
            System.out.println("数据库中使用的表类型:");
            resultSet = dbmd.getTableTypes();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("TABLE_TYPE"));
            }
        } catch (SQLException e) {
            System.out.println("获取数据库相关信息失败：" + e);
        } finally {
            close(resultSet);
        }
    }

    // 8.关闭连接
    public static void close(Object o) {
        if (Objects.isNull(o)) {
            return;
        }
        try {
            if (o instanceof ResultSet) {
                ((ResultSet) o).close();
            } else if (o instanceof Statement) {
                ((Statement) o).close();
            } else if (o instanceof Connection) {
                Connection con = (Connection) o;
                if (!con.isClosed()) {
                    con.close();
                }
            }
        } catch (SQLException e) {
            System.out.println("关闭连接失败：" + e);
        }
    }

    // 测试
    public static void main(String[] args) throws SQLException {

        // 对应url、userName、password需换成自己配置的内容
        /*System.out.println("======================");
        getDataBase("jdbc:mysql://127.0.0.1:3306?serverTimezone=UTC", "root", "root");
        System.out.println("======================");
        getTables("jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "root");
        System.out.println("======================");
        getColumns("u_user", "jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "root");
        System.out.println("======================");
        getRows("u_user", "jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC", "root", "root");
        System.out.println("======================");
        getDataBaseInfo("jdbc:mysql://127.0.0.1:3306?serverTimezone=UTC", "root", "root");
        System.out.println("======================");*/

        getColumnInfos("biz_leave", "jdbc:mysql://127.0.0.1:3306/ry_act_all?serverTimezone=UTC", "root", "root");
        getColumnInfos("biz_todo_item", "jdbc:mysql://127.0.0.1:3306/ry_act_all?serverTimezone=UTC", "root", "root", 7);

    }

    // 9. 获取表的字段及类型
    public static void getColumnInfos(String tableName, String url, String userName, String password, Integer... gapLen) {
        System.out.println();
        String query = "SELECT * FROM " + tableName;

        try (Connection connection = getConnection(url, userName, password);
             PreparedStatement statement = connection.prepareStatement(query);
             PreparedStatement statement2 = connection.prepareStatement(query)) {


            TableInfo tableInfo = new TableInfo();

            // 查询表的注释
            String url1 = connection.getMetaData().getURL();
            Matcher matcher = Pattern.compile(".*?3306/([^?]+)\\??").matcher(url1);
            if (matcher.find()) {
                String dbName = matcher.group(1);
                ResultSet rs = statement2.executeQuery("SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + dbName+ "' AND TABLE_NAME = '"+ tableName +"'");

                if (rs.next()) {
                    // String tableComment = rs.getString("TABLE_COMMENT");
                    // System.out.println("Table Comment: " + tableComment);
                    tableInfo.setTableComment(rs.getString("TABLE_COMMENT"));
                }
                rs.close();
            }
            statement2.close();

            ResultSetMetaData resultSetMetaData = statement.getMetaData();


            List<ColumnInfo> columnInfos = new ArrayList<>();
            tableInfo.setColumnInfos(columnInfos);

            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {

                ColumnInfo columnInfo = new ColumnInfo();
                columnInfos.add(columnInfo);

                // System.out.println("ColumnName : " + resultSetMetaData.getColumnName(i + 1));
                // System.out.println("ColumnTypeName : " + resultSetMetaData.getColumnTypeName(i + 1));

                columnInfo.setColumnName(resultSetMetaData.getColumnName(i + 1));

                // 获取字段注释
                DatabaseMetaData dbmd = connection.getMetaData();
                ResultSet columnResultSet = dbmd.getColumns(null, null, tableName, resultSetMetaData.getColumnLabel(i + 1));
                if (columnResultSet.next()) {
                    String columnComment = columnResultSet.getString("REMARKS");

                    columnInfo.setColumnComment(columnComment);

                } else {
                    System.out.println("columnComment : " + "");

                    columnInfo.setColumnComment("");
                }

            }

            int maxLen = 0;
            for (ColumnInfo columnInfo : tableInfo.getColumnInfos()) {
                if (columnInfo.getColumnName().length() > maxLen) {
                    maxLen = columnInfo.getColumnName().length();
                }
            }


            int gapChars = gapLen == null || gapLen.length == 0 ? 5 : gapLen[0];

            int maxLenPlusGapChars = maxLen + gapChars;

            System.out.println(tableName + "   " + tableInfo.getTableComment());

            for (ColumnInfo columnInfo : tableInfo.getColumnInfos()) {
                System.out.print(columnInfo.getColumnName());
                for (int i = 0; i < maxLenPlusGapChars - columnInfo.getColumnName().length(); i++) {
                    System.out.print(" ");
                }
                System.out.println(columnInfo.getColumnComment());
            }


        } catch (SQLException e) {
            System.out.println("获取数据库表字段和类型失败：" + e);
        }
    }

    @Data
    public static class TableInfo {
        private String tableComment;
        private List<ColumnInfo> columnInfos;
    }

    @Data
    private static class ColumnInfo {
        private String columnName;
        private String columnComment;
    }

}
